<?php
include_once 'MY/extra_sql.php';
include_once 'MY/excelreader.class.php';
include_once 'MY/lib_splitword_full.php';
include_once 'PHPExcel.php';
include_once 'PHPExcel/IOFactory.php';
class ExcelController extends Zend_Controller_Action{
	function init(){
		$this->registry = Zend_Registry::getInstance();
		$this->view = $this->registry['view'];
		$this->view->baseUrl = $this->_request->getBaseUrl();
		$this->view->template = "system";
	}

	public function indexAction(){
		$this->view->step = isset($_GET['step'])?$_GET['step']:1;
		echo $this->view->render('excel/index.html');
	}
	
	//表所有字段
	public function detailAction(){
		$filter=new Zend_Filter_StripTags();
		$db = new mysql(constant('dbhost'),constant('dbuser'),constant('dbpass'),constant('dbname'),"UTF8");
		//取得模块名
		$model=$filter->filter(($this->_request->getParam('model')));
		$fields = $db->describe($model);

		$this->view->fields = $fields;
		$this->view->model = $model;
		echo $this->view->render('excel/detail.html');
	}
	
	//添加字段
	public function addAction(){
		$filter=new Zend_Filter_StripTags();
		$db = new mysql(constant('dbhost'),constant('dbuser'),constant('dbpass'),constant('dbname'),"UTF8");
		$model=$filter->filter($this->_request->getPost('model'));
		$column_name=$filter->filter($this->_request->getPost('column_name'));
		$column_comment=$filter->filter($this->_request->getPost('column_comment'));

		$query = $db->add_field($model, $column_name, $column_comment);
		if($query){
			echo "Success!";
		}else{
			echo "Failure!";
		}
	}
        
	//修改字段
	public function editAction(){
		$filter=new Zend_Filter_StripTags();
		$db = new mysql(constant('dbhost'),constant('dbuser'),constant('dbpass'),constant('dbname'),"UTF8");
		$model=$filter->filter($this->_request->getParam('model'));

		if(strtolower($_SERVER['REQUEST_METHOD'])=='post'){
			$column_name=$filter->filter($this->_request->getPost('column_name'));
			$before_name=$filter->filter($this->_request->getPost('before_name'));
			$column_comment=$filter->filter($this->_request->getPost('column_comment'));
			$query = $db->edit_field($model, $before_name, $column_name, $column_comment);
                
			if($query){
				echo "<script>window.location='detail?model=".$model."'</script>";
			}else{
				echo "<script>alert('Failure!');history.go('-1');</script>";
			}
		}else{
			$column_name=$filter->filter($this->_request->getParam('column_name'));
			$column_comment=$filter->filter($this->_request->getParam('column_comment'));
			$this->view->column_name = $column_name;
			$this->view->column_comment = $column_comment;
			$this->view->model = $model;
		}
		echo $this->view->render('excel/edit.html');
	}

	//删除字段
	public function deleteAction(){
		$filter=new Zend_Filter_StripTags();
		$db = new mysql(constant('dbhost'),constant('dbuser'),constant('dbpass'),constant('dbname'),"UTF8");
		$model=$filter->filter($this->_request->getPost('model'));
		$column_name=$filter->filter($this->_request->getPost('column_name'));
		$query = $db->delete_field($model, $column_name);
		if($query){
			echo "Success!";
		}else{
			echo "Failure!";
		}
	}

	//导入数据
	public function uploadAction(){
		date_default_timezone_set('Asia/Shanghai') ;
		$filter=new Zend_Filter_StripTags();
		$company = new Company();
		$classes = new Classes();
		$teacher = new Teacher();
		$student = new Student();
		$department = new Department();
		$db = new mysql(constant('dbhost'),constant('dbuser'),constant('dbpass'),constant('dbname'),"UTF8");
		$model=$filter->filter($this->_request->getPost('model'));
		$path = "public/uploads/";
		$name = $_FILES['files']['name'];
		$path=$path.$name;
		move_uploaded_file($_FILES["files"]["tmp_name"],$path);
		if(is_readable($path)){
			$data = new Spreadsheet_Excel_Reader();
			$data->setOutputEncoding('UTF-8');
			$data->read($path);
			
			$filmarray = $data->sheets[0]['cells'];	
		}else{
			return "文件不可读";
		}
		$fields = $db->describe($model);
//		echo '<pre>';
//		print_r($fields);
		$data = array();
		$count = count($fields);
		$j=0;
		foreach($filmarray as $film){
			
			if ($j>0) {
				for($i=1;$i<$count;$i++){
					if($film[$i]==null){
						$film[$i]='';
					}

					if($fields[$i]['COLUMN_NAME']=="start_time"||$fields[$i]['COLUMN_NAME']=="end_time"||$fields[$i]['COLUMN_NAME']=="birthday"){
						if (!empty($film[$i])){
						    $data[$fields[$i]['COLUMN_NAME']] = strtotime(str_replace('/','-',$film[$i]));
							//$data[$fields[$i]['COLUMN_NAME']] = strtotime(str_replace('/','-',$film[$i]))-24*60*60;
						}else{
							$data[$fields[$i]['COLUMN_NAME']] = 0;
						}
					}elseif($fields[$i]['COLUMN_NAME']=="create_time"||$fields[$i]['COLUMN_NAME']=="update_time"){
						$data[$fields[$i]['COLUMN_NAME']] = time();
					}elseif($fields[$i]['COLUMN_NAME']=="company_id"&&$model!='company'){
						$arrCompany = $company->getCompanyBycompany_name($film[$i]);
						if (!empty($arrCompany)) {
							$data[$fields[$i]['COLUMN_NAME']] = $arrCompany[0]['company_id'];
						}else{
							$data[$fields[$i]['COLUMN_NAME']] = 0;
						}	
					}elseif($fields[$i]['COLUMN_NAME']=="class_id"){
						$arrClass = $classes->getClassByclass_name($film[$i]);
						if (!empty($arrClass)) {
							$data[$fields[$i]['COLUMN_NAME']] = $arrClass[0]['class_id'];
						}else{
							$data[$fields[$i]['COLUMN_NAME']] = 0;
						}
					}elseif($fields[$i]['COLUMN_NAME']=="teacher_id"){
						$arrTeacher = $teacher->getTeacherByteacher_name($film[$i]);
						if (!empty($arrTeacher)) {
							$data[$fields[$i]['COLUMN_NAME']] = $arrTeacher[0]['teacher_id'];
						}else{
							$data[$fields[$i]['COLUMN_NAME']] = 0;
						}
					}elseif($fields[$i]['COLUMN_NAME']=="student_id"){
						$arrStudent = $student->getStudentBystudent_name($film[$i]);
						if (!empty($arrStudent)) {
							$data[$fields[$i]['COLUMN_NAME']] = $arrStudent[0]['student_id'];
						}else{
							$data[$fields[$i]['COLUMN_NAME']] = 0;
						}
					}elseif($fields[$i]['COLUMN_NAME']=="department_id"){
					    $departments = $department->getDepartmentBydepartment_name($film[$i]);
						if (!empty($departments)) {
							$data[$fields[$i]['COLUMN_NAME']] = $departments[0]['id'];
						}else{
							$data[$fields[$i]['COLUMN_NAME']] = 0;
						}
						
					}else{
						$data[$fields[$i]['COLUMN_NAME']] = $film[$i];	
	//项目标题关键字					
//						if($model=='project'&&$fields[$i]['COLUMN_NAME']=="title"){
//							if (!empty($film[$i])){
//								            $sp = new SplitWord();
//								            $str = iconv('utf-8','gb2312',$film[$i]);
//								            $str = $sp->SplitRMM($str);
//								            $str = iconv('gb2312','utf-8',$str);
//								$data['key_word'] = $str;   
//								$data[$fields[$i]['COLUMN_NAME']] = $film[$i];	         
//							}
//						}else if($fields[$i]['COLUMN_NAME']!="key_word"){
//						  $data[$fields[$i]['COLUMN_NAME']] = $film[$i];	
//						}
					if($model=='company'&&$fields[$i]['COLUMN_NAME']=='company_id'){
                        	unset($data[$fields[$i]['COLUMN_NAME']]);
                        }
					}
				}		
				if(($data['name']!='')||($model=='project'&&$data['title']!='')||($model=='student_class')||($model=='class_teacher')){
				
					$query =$db->insert($model,$data);
					if($query!=1){
						echo "可能字段名存在问题，sql语句为:".$query;
						exit;
					}
				}
			}
			++$j;
		}
		echo "<script>window.history.go(-1);</script>";
	}    
}

?>
